package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 根据输入的老师名字，查看该老师的名字，工资，职位，所带班级的名字，楼层
 * 以及班里每个学生的名字，年龄，性别
 */
public class JDBCDemo13 {
    public static void main(String[] args) {
        Scanner scan = new Scanner(System.in);
        System.out.println("请输入老师名字:");
        String name = scan.next();
         try ( Connection connection = DBUtil.getConnection()){
              String sql = "SELECT t.name, t.salary, t.title, c.name AS class_name, c.floor, s.name AS student_name, s.age, s.gender " +
                    "FROM teacher t " +
                    "LEFT JOIN class c ON t.id = c.teacher_id " +
                    "LEFT JOIN student s ON c.id = s.class_id " +
                    "WHERE t.name = ?";
             PreparedStatement st = connection.prepareStatement(sql);
             st.setString(1, name);
             ResultSet rs = st.executeQuery();
             while (rs.next()) {
                 System.out.println("老师名字:" + rs.getString("t.name"));
                 System.out.println("老师工资:" + rs.getString("t.salary"));
                 System.out.println("老师职位:" + rs.getString("t.title"));
                 System.out.println("老师所带班级:" + rs.getString("class_name"));
                 System.out.println("老师所带班级楼层:" + rs.getString("c.floor"));
                 System.out.println("老师所带班级的成员:");
                 while (rs.next()) {
                     System.out.println("成员名字:" + rs.getString("student_name"));
                     System.out.println("成员年龄:" + rs.getString("s.age"));
                     System.out.println("成员性别:" + rs.getString("s.gender"));
                          }
             }

         } catch (SQLException e) {
             throw new RuntimeException(e);
         }

    }
}
